package persistence.dataAccessObjects;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.HashMap;
import java.util.LinkedHashMap;

import javax.servlet.ServletException;

import persistence.dataAccessObjects.interfaces.IStatisticsDAO;

import exceptions.DatasetNotFoundException;

public class StatisticsDAO extends GenericDAO implements IStatisticsDAO {

	public StatisticsDAO() 
	{
		super();
	}


	public LinkedHashMap<String, Integer> getUserCount() throws ServletException,

			DatasetNotFoundException {

		String sqlQuery = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		LinkedHashMap<String, Integer> results = new LinkedHashMap<String, Integer>();
		Integer count = new Integer(0);
		String land = null;

		try {
			connection = getConnection(true);

			sqlQuery = "SELECT land,COUNT(*) AS count FROM user GROUP BY land ORDER BY count DESC";

			pstmt = connection.prepareStatement(sqlQuery);

			rs = pstmt.executeQuery();
			while (rs.next()) {

				land = rs.getString("land");
				count = rs.getInt("count");


				results.put(land, count);
				

			}
			
		
			
		
		}

		catch (SQLException e) {
			e.printStackTrace();
			throw new ServletException(e.getMessage());
		}

		finally {
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
			}
			try {
				if (pstmt != null)
					pstmt.close();
			} catch (SQLException e) {
			}
			try {
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
			}
		}
		return results;

	}

	
	
	public LinkedHashMap<Integer, Integer> getSoldProductsCountSK(int year)
			throws ServletException, DatasetNotFoundException {

		String sqlQuery = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Integer count = 0;
		Integer month = 0;

		LinkedHashMap<Integer, Integer> results = new LinkedHashMap<Integer, Integer>();

		try {
			connection = getConnection(true);

			sqlQuery = "SELECT count(*) AS count FROM produkte "
					+ "JOIN sofortkaufAngebote ON produkte.ID = sofortkaufAngebote.PID "
					+ "JOIN sofortkaeufe ON sofortkaeufe.SKAID=sofortkaufAngebote.SKAID "
					+ "WHERE sofortkaeufe.bezahlt=1 AND YEAR(sofortkaeufe.kaufdatum)=? AND MONTH(sofortkaeufe.kaufdatum)=?";

			pstmt = connection.prepareStatement(sqlQuery);
			pstmt.setInt(1, year);

			for (int i = 1; i < 13; ++i) {
				pstmt.setInt(2, i);
				rs = pstmt.executeQuery();

				while (rs.next()) {

					month = i;
					count = rs.getInt("count");

					results.put(month, count);


				}
			}

		}
		
		catch (SQLException e) {
			e.printStackTrace();
			throw new ServletException(e.getMessage());
		}

		finally {
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
			}
			try {
				if (pstmt != null)
					pstmt.close();
			} catch (SQLException e) {
			}
			try {
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
			}
		}
		return results;
			
	}
	
	public LinkedHashMap<Integer, Integer> getSoldProductsCountAuktion(int year) throws ServletException, DatasetNotFoundException
	{
		String sqlQuery = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Integer count = 0;
		Integer month = 0;
		
		LinkedHashMap<Integer, Integer> results = new LinkedHashMap<Integer, Integer>();
		
		try {
			connection = getConnection(true);
			
			sqlQuery = "SELECT count(*) AS count FROM auktionen " + "WHERE auktionen.bezahlt = 1 AND YEAR(auktionen.enddatum)=? AND MONTH(auktionen.enddatum)=?";
			
			pstmt = connection.prepareStatement(sqlQuery);
			pstmt.setInt(1, year);
			
			for (int i = 1; i < 13; ++i) {
				pstmt.setInt(2, i);
				rs = pstmt.executeQuery();
				
				while (rs.next()) {
					
					month = i;
					count = rs.getInt("count");
					
					results.put(month, count);
					
				}
			}
			
		}
		catch (SQLException e) {
			e.printStackTrace();
			throw new ServletException(e.getMessage());
		}
		
		finally {
			try {
				if (rs != null) rs.close();
			}
			catch (SQLException e) {}
			try {
				if (pstmt != null) pstmt.close();
			}
			catch (SQLException e) {}
			try {
				if (connection != null) connection.close();
			}
			catch (SQLException e) {}
		}
		
		return results;
		
	}

}